﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;
using System.Data.Common;
using System.Data.OracleClient;
using MySql.Data.MySqlClient;

namespace CWCS.DataAccess
{
    public class DALDATA : DALBase
    {

        public int GetTaskCount(string toStation)
        {
            string sql = @"select count(1) from task t where t.targetaddresstext = '{toStation}'";
            DataSet ds = DataAccessFactory.Instance.ExecuteDataSet(CommandType.Text, sql);
            if (ds == null || ds.Tables[0] == null || ds.Tables[0].Rows.Count < 1 || ds.Tables[0].Rows[0][0].ToString().Equals("0"))
            {
                return 0;
            }
            return Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
        }

        public string GetSortEquipmentID()
        {
            string str = "select equipmentid from equipment where warehouseid like '8%' and equipmentnumber like 'Sort%'";
            return DataAccessFactory.Instance.ExecuteScalar(CommandType.Text, str, null).ToString();
        }

        public DataSet GetDockID()
        {
            string str = "select dockid,docknumber from dock where warehouseid like '8%' and docknumber like 'DockOut%' order by docknumber";
            return DataAccessFactory.Instance.ExecuteDataSet(str, CommandType.Text, null);
        }




        public bool UpdateTaskStatusByTaskno(string taskno, string fromstation, string tostation)
        {
            string str = "update wcs_subtaskinfo set taskstatus='2' where warehouseid = '" + WareHouseID + "' and taskno='" + taskno + "' and equipmentType = 'AGV' and fromstation='" + fromstation + "' and tostation='" + tostation + "'";
            int i = DataAccessFactory.Instance.ExecuteNonQuery(CommandType.Text, str, null);
            if (i > 0)
                return true;
            return false;
        }

        public bool UpdateAGVIndex(string taskno, string index)
        {
            string str = "update wcs_subtaskinfo set agvindex='" + index + "' where warehouseid = '" + WareHouseID + "' and taskno like '%" + taskno + "%' and equipmentType = 'AGV'";
            int i = DataAccessFactory.Instance.ExecuteNonQuery(CommandType.Text, str, null);
            if (i > 0)
                return true;
            return false;
        }
        /// <summary>
        /// 货叉1获取任务后，更新输送机站台地址
        /// </summary>
        /// <param name="WareHouseID"></param>
        /// <param name="TaskNo"></param>
        /// <param name="BarCode"></param>
        /// <param name="TaskType"></param>
        /// <param name="TaskStatus"></param>
        /// <param name="RunStatus"></param>
        /// <returns></returns>
        protected bool UpdateTaskStation(string WareHouseID, string TaskNo, string BarCode, string i_equipmenttype, string i_tostation, string i_fromstation, string i_SCMODE, ref string o_tostation)
        {
            OracleParameter[] parms = new OracleParameter[]
            {
                new OracleParameter(@"i_warehouseid", OracleType.VarChar, 50),
                new OracleParameter(@"i_barcode", OracleType.VarChar, 50),
                new OracleParameter(@"i_taskno", OracleType.VarChar, 50),
                new OracleParameter(@"i_equipmenttype", OracleType.VarChar, 50),
                new OracleParameter(@"i_tostation", OracleType.VarChar, 50),
                new OracleParameter(@"i_fromstation", OracleType.VarChar, 50),
                new OracleParameter(@"i_parentlognoid", OracleType.VarChar, 50),
                new OracleParameter(@"i_scmode", OracleType.VarChar, 50),
                new OracleParameter(@"o_tostation", OracleType.VarChar,50),
                new OracleParameter(@"o_result", OracleType.Int32)
            };

            parms[0].Direction = ParameterDirection.Input;
            parms[0].Value = WareHouseID;
            parms[1].Direction = ParameterDirection.Input;
            parms[1].Value = string.IsNullOrEmpty(BarCode) ? "0000000000" : BarCode;
            parms[2].Direction = ParameterDirection.Input;
            parms[2].Value = TaskNo;
            parms[3].Direction = ParameterDirection.Input;
            parms[3].Value = i_equipmenttype;
            parms[4].Direction = ParameterDirection.Input;
            parms[4].Value = i_tostation;
            parms[5].Direction = ParameterDirection.Input;
            parms[5].Value = i_fromstation;
            parms[6].Direction = ParameterDirection.Input;
            parms[6].Value = "";
            parms[7].Direction = ParameterDirection.Input;
            parms[7].Value = i_SCMODE;
            parms[8].Direction = ParameterDirection.Output;
            parms[8].Value = o_tostation;
            parms[9].Direction = ParameterDirection.Output;
            parms[9].Value = 0;
            int i = DataAccessFactory.Instance.ExecuteNonQuery("cwcs_updatetaskstation_p", CommandType.StoredProcedure, parms);
            if (System.String.IsNullOrEmpty(parms[8].Value.ToString()) || parms[9].Value.ToString() == "0")
            {
                string a = parms[8].Value.ToString();

                return false;
            }
            o_tostation = parms[8].Value.ToString().Trim();
            return true;
        }

        public int Update_Equipment_Lotnumber(string equipmentnumber, string Lotnumer, string IgnoreFlag)
        {
            int k = 0;
            OracleParameter[] parms = new OracleParameter[]
           {
            new OracleParameter(PARAM_WAREHOUSE_ID,OracleType.VarChar,50),
            new OracleParameter(PARAM_EQUIPMENTNUMBER,OracleType.VarChar,10),
            new OracleParameter(":LOTNUMBER",OracleType.VarChar,20),
            new OracleParameter(":FLAG",OracleType.VarChar,20)
            };
            parms[0].Value = WareHouseID;
            parms[1].Value = equipmentnumber;
            parms[2].Value = Lotnumer;
            parms[3].Value = IgnoreFlag;

            return DataAccessFactory.Instance.ExecuteNonQuery(CommandType.Text, SQL_UPDATE_LOTNUMBER, parms);
        }

        public DataSet GetOutboundTask(string TaskNo)
        {
            string str = "select taskno,tostation,barcode,fromstation from wcs_subtaskinfo"
                 + " where warehouseid='" + WareHouseID + "'"
                 + " and TaskNo = '" + TaskNo + "'"
                 + " and equipmenttype = 'STA'"
                 + " and runstatus = '1' order by createtime desc";

            return DataAccessFactory.Instance.ExecuteDataSet(str, CommandType.Text, null);
        }

        public string GetItemCodeinfo(string TaskNo)
        {
            string str = "select code from item where itemid in (select itemno from Wcs_Taskinfo where taskno = '" + TaskNo + "' and rownum < 2)";

            object ob = DataAccessFactory.Instance.ExecuteScalar(CommandType.Text, str, null);
            if (ob != null)
                return ob.ToString();
            else
                return "";
        }

        public string QueryTaskCount(string TaskNo)
        {
            OracleParameter[] parms = new OracleParameter[] {
             new OracleParameter(PARAM_WAREHOUSE_ID,OracleType.VarChar,50),
             new OracleParameter(PARAM_TASKNO,OracleType.VarChar,10)
            };
            parms[0].Value = WareHouseID;
            parms[1].Value = TaskNo;
            string str = @"with cte as(select t.commonnumber,t.warehouseid from task t inner join wcs_taskinfo wt on t.tasknumber = wt.wmstaskno and t.warehouseid = wt.warehouseid
              where t.warehouseid =:WareHouseId and wt.taskno =:TaskNo and rownum <=1) select count(1) from task inner join cte on task.warehouseid = cte.warehouseid and task.commonnumber = cte.commonnumber
              where task.warehouseid =:WareHouseID";

            object ob = DataAccessFactory.Instance.ExecuteScalar(CommandType.Text, str, parms);
            if (ob != null)
                return ob.ToString();
            else
                return "";
        }

        /// <summary>
        /// 查询当前1128站台申请任务信息。
        /// </summary>
        /// <returns></returns>
        public DataSet QueryCurrenOutTask(string TaskNo)
        {
            OracleParameter[] parms = new OracleParameter[] {
             new OracleParameter(PARAM_WAREHOUSE_ID,OracleType.VarChar,50),
             new OracleParameter(PARAM_TASKNO,OracleType.VarChar,10)
            };
            parms[0].Value = WareHouseID;
            parms[1].Value = TaskNo;
            return DataAccessFactory.Instance.ExecuteDataSet(SQL_SELECT_CurrentTask1128, CommandType.Text, parms);
        }
        /// <summary>
        /// 根据托盘好查询当前托盘信息
        /// </summary>
        /// <param name="palletnumber">托盘号</param>
        /// <returns>托盘信息</returns>
        public DataSet QueryCurrentTaskinfo(string palletnumber)
        {
            //string[] currentinfo=new string[4];
            string temdata = "";
            string str = "select t.containerbarcode,t.sourceaddresstext,t.targetaddresstext,t.ledtext,t.tasktypecode from task t where t.warehouseid = '57274b9c55464753b8bd7a0df96421f2' and t.containerbarcode='" + palletnumber + "'";
            return DataAccessFactory.Instance.ExecuteDataSet(str, CommandType.Text, null);
        }

        /// <summary>
        /// 根据任务号查询WCS历史任务
        /// </summary>
        /// <param name="TaskNo">任务号</param>
        /// <returns></returns>
        public string QueryWCSHisTask(string TaskNo)
        {
            OracleParameter[] parms = new OracleParameter[]{
               new OracleParameter(PARAM_WAREHOUSE_ID,OracleType.VarChar,50),
             new OracleParameter(PARAM_TASKNO,OracleType.VarChar,10)
            };
            parms[0].Value = WareHouseID;
            parms[1].Value = TaskNo;
            string str = "select i.code||';'||oh.lotnumber||';'||i.name from wcs_taskinfo_history w left join finishedtask ft on ft.tasknumber=w.wmstaskno  Inner join Orderhead oh  On ft.commondetailnumber = oh.ordernumber   left join item i on i.itemid=w.itemno where   w.createtime>sysdate-5 and w.warehouseid =:WareHouseID and w.taskno =:TaskNo and rownum<2";
            object ob = DataAccessFactory.Instance.ExecuteScalar(CommandType.Text, str, parms);
            if (ob != null)
            {
                return ob.ToString();
            }
            else
                return "0";
        }
        public void AddLog(string logtext)
        {
            OracleParameter[] parms = new OracleParameter[]
            {
                new OracleParameter(@"i_warehouseid", OracleType.VarChar, 50),
                new OracleParameter(@"i_logtext", OracleType.VarChar, 200)
            };
            parms[0].Direction = ParameterDirection.Input;
            parms[0].Value = WareHouseID;
            parms[1].Direction = ParameterDirection.Input;
            parms[1].Value = logtext;
            DataAccessFactory.Instance.ExecuteNonQuery(CommandType.StoredProcedure, "WCS_CREATEEVENTLOG_P", parms);
        }
        public DataSet GetTaskLotnumber(string taskno)
        {
            string lotnumber = string.Empty;
            OracleParameter[] parms = new OracleParameter[]
            {
                 new OracleParameter(PARAM_WAREHOUSE_ID,OracleType.VarChar,50),
                  new OracleParameter(PARAM_TASKNO,OracleType.VarChar,10)
            };
            parms[0].Value = WareHouseID;
            parms[1].Value = taskno;
            return DataAccessFactory.Instance.ExecuteDataSet(SQL_SELECT_TASKLOTNUMBER, CommandType.Text, parms);
        }

        public string SCtostation(string TaskNo)
        {
            string str = @"select wcs_subtaskinfo.tostation from wcs_subtaskinfo where  taskno='" + TaskNo + "' and  equipmenttype='SC'";
            object ob = DataAccessFactory.Instance.ExecuteScalar(CommandType.Text, str, null);
            if (ob != null)
                return ob.ToString();
            else
                return null;
        }

        public string Tasknotype(string TaskNo)
        {
            string sql;
            object ob = null;
            string str = "select wcs_taskinfo.wmstaskno from wcs_taskinfo where  taskno='" + TaskNo + "'";
            object i = DataAccessFactory.Instance.ExecuteScalar(CommandType.Text, str, null);
            if (i != null)
            {
                if (i.ToString() == "99999")
                {
                    sql = @"select wcs_taskinfo.tostation from wcs_taskinfo where  taskno='" + TaskNo + "'";
                    ob = DataAccessFactory.Instance.ExecuteScalar(CommandType.Text, sql, null);
                }
                if (ob != null)
                    return ob.ToString();
                else
                    return null;
            }
            return null;
        }
        public DataSet GetOPCItem()
        {
            OracleParameter[] parms = new OracleParameter[]
            {
                 new OracleParameter(PARAM_WAREHOUSE_ID,OracleType.Char,32)
            };
            parms[0].Value = WareHouseID;
            return DataAccessFactory.Instance.ExecuteDataSet(SQL_SELECT_OPCITEM, CommandType.Text, parms);
        }

        public DataSet GetEquipmentInfo()
        {
            MySqlParameter[] parms = new MySqlParameter[]
         {
                 new MySqlParameter(PARAM_WAREHOUSE_ID,MySqlDbType.VarChar,32),
                 new MySqlParameter(PARAM_ZoneCode,MySqlDbType.VarChar,32)
         };
            parms[0].Value = WareHouseID;
            parms[1].Value = ZoneCode;
            return DataAccessFactory.Instance.ExecuteDataSet(SQL_SELECT_WCSEQUIPMENT, CommandType.Text, parms);
        }

        //public bool FlowControl(int type, string taskNo, string formStationNo, string toStationNo, string barCode)
        //{
        //    OracleParameter[] parms = new OracleParameter[]
        //    {
        //        new OracleParameter(@"i_type", OracleType.Int32),
        //        new OracleParameter(@"i_taskno", OracleType.VarChar, 255),
        //        new OracleParameter(@"i_formstationno", OracleType.VarChar,255),
        //        new OracleParameter(@"i_tostationno", OracleType.VarChar,255),
        //        new OracleParameter(@"i_containerbarcode", OracleType.VarChar,255),
        //        new OracleParameter(@"o_result", OracleType.Int32),
        //        new OracleParameter(@"o_msg", OracleType.VarChar,1000)
        //    };
        //    parms[0].Direction = ParameterDirection.Input;
        //    parms[0].Value = type;
        //    parms[1].Direction = ParameterDirection.Input;
        //    parms[1].Value = taskNo;
        //    parms[2].Direction = ParameterDirection.Input;
        //    parms[2].Value = formStationNo;
        //    parms[3].Direction = ParameterDirection.Input;
        //    parms[3].Value = toStationNo;
        //    parms[4].Direction = ParameterDirection.Input;
        //    parms[4].Value = barCode;
        //    parms[5].Direction = ParameterDirection.Input;
        //    parms[5].Value = 0;
        //    parms[6].Direction = ParameterDirection.Output;
        //    parms[6].Value = "";
        //    int i = DataAccessFactory.Instance.ExecuteNonQuery("CWCS_FLOW_CONTROL", CommandType.StoredProcedure, parms);
        //    if (parms[6].Value.ToString() == "1")
        //    {
        //        return false;
        //    }
        //    return true;
        //}

        public bool UpdateTaskStatus(string fromstation, string TaskNo, string BarCode, string TaskType, string TaskStatus, string RunStatus)
        {
            MySqlParameter[] parms = new MySqlParameter[]
            {
                new MySqlParameter(@"i_warehouseid", MySqlDbType.VarChar, 50),
                new MySqlParameter(@"i_fromstation", MySqlDbType.VarChar, 50),
                new MySqlParameter(@"i_barcode", MySqlDbType.VarChar, 50),
                new MySqlParameter(@"i_taskno", MySqlDbType.VarChar, 50),
                new MySqlParameter(@"i_equipmenttype", MySqlDbType.VarChar, 10),
                new MySqlParameter(@"i_taskstatus", MySqlDbType.VarChar),
                new MySqlParameter(@"i_runstatus", MySqlDbType.VarChar, 10),
                new MySqlParameter(@"i_parentlognoid", MySqlDbType.VarChar,32),
                new MySqlParameter(@"o_result", MySqlDbType.Int32)
            };
            parms[0].Direction = ParameterDirection.Input;
            parms[0].Value = WareHouseID;
            parms[1].Direction = ParameterDirection.Input;
            parms[1].Value = fromstation;
            parms[2].Direction = ParameterDirection.Input;
            parms[2].Value = BarCode;
            parms[3].Direction = ParameterDirection.Input;
            parms[3].Value = TaskNo;
            parms[4].Direction = ParameterDirection.Input;
            parms[4].Value = TaskType;
            parms[5].Direction = ParameterDirection.Input;
            parms[5].Value = TaskStatus;
            parms[6].Direction = ParameterDirection.Input;
            parms[6].Value = RunStatus;
            parms[7].Direction = ParameterDirection.Input;
            parms[7].Value = "";
            parms[8].Direction = ParameterDirection.Output;
            parms[8].Value = 0;
            int i = DataAccessFactory.Instance.ExecuteNonQuery("CWCS_UPDATETASKSTATUS_P", CommandType.StoredProcedure, parms);
            if (System.String.IsNullOrEmpty(parms[8].Value.ToString()) || parms[8].Value.ToString() == "0")
            {
                return false;
            }
            return true;
        }

        public DataSet GetSRMTask(string scstatus)
        {
            MySqlParameter[] parms = new MySqlParameter[]
               {
                    new MySqlParameter(@"i_warehouseid", MySqlDbType.VarChar, 50),
                    new MySqlParameter(@"i_scstatus", MySqlDbType.VarChar, 50),
               };
            parms[0].Direction = ParameterDirection.Input;
            parms[0].Value = WareHouseID;
            parms[1].Direction = ParameterDirection.Input;
            parms[1].Value = scstatus;
            return DataAccessFactory.Instance.ExecuteDataSet("cwcs_scgettask_hc", CommandType.StoredProcedure, parms);
        }

        //堆垛机获取任务
        public DataSet GetM6USRMTask(string equipmentnumber)
        {
            DataSet ds = null;
           // ds = DataAccessFactory.Instance.ExecuteDataSet("select * from wcs_subtaskinfo where equipmentnumber = '" + equipmentnumber + "'", CommandType.Text, null);
          ds = DataAccessFactory.Instance.ExecuteDataSet("select * from view_sctask where equipmentnumber = '" + equipmentnumber + "'", CommandType.Text, null);
            return ds;
        }
        public DataSet GetTaskByEquipmentnumber(string equipmentnumber)
        {
            DataSet ds = null;
            // ds = DataAccessFactory.Instance.ExecuteDataSet("select * from wcs_subtaskinfo where equipmentnumber = '" + equipmentnumber + "'", CommandType.Text, null);
            ds = DataAccessFactory.Instance.ExecuteDataSet("select * from view_task where equipmentnumber = '" + equipmentnumber + "'", CommandType.Text, null);
            return ds;
        }
        //M6U堆垛机获取任务
        public DataSet GetOpcitemInfo()
        {
            DataSet ds = null;
            ds = DataAccessFactory.Instance.ExecuteDataSet("select a.* from wcs_opcitem a   where  a.GROUPNAME='Convery'", CommandType.Text, null);
            return ds;
        }
        public DataSet GetAdjustInfo()
        {
            DataSet ds = null;
            ds = DataAccessFactory.Instance.ExecuteDataSet("  SELECT l.locationnumber as adjustLocationnumber,l1.locationnumber as scanLocationnumber,l1.exceptionstation as station FROM location  l LEFT JOIN location l1 ON 1=1 " +
                "WHERE l.locationflag = 'LocationFlag_Adjust' AND l1.locationflag = 'LocationFlag_Scan'", CommandType.Text, null);
            return ds;
        }
        public string getFire(string equipmentnumber, ref bool isFire)
        {
            string sql = "select  count(1),locationnumber from location l join equipment e on l.per_equipmentid=e.equipmentid where outflag='H'  AND e.equipmentnumber='" + equipmentnumber + "' and locationstate ='LocationState_Stored' limit 1";
            try
            {
                DataSet ds = null;
                ds = DataAccessFactory.Instance.ExecuteDataSet(sql, CommandType.Text, null);
                if (ds == null || ds.Tables[0].Rows.Count < 1 || ds.Tables[0].Rows[0][0].ToString() == "0")
                {
                    isFire = false;
                    return "";
                }
                else
                {
                    isFire = true;
                    return ds.Tables[0].Rows[0][1].ToString();
                }
            }
            catch (Exception e)
            {
                isFire = false;
                return "";
            }
        }
        /// <summary>
        /// 清除火警库位信息
        /// </summary>
        /// <param name="locationnumber"></param>
        /// <returns></returns>
        public bool ClearFireflagLocationInfo(string locationnumber)
        {
            String date = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
            string str = "update location set outflag='',ContainerID = '',ContainerBarcode = '',BOMID = '',LocationState = 'LocationState_Empty',LocationFlag = 'LocationFlag_Disabled',Version = '',remarks = '" + date + "发生火警库位,请解除火警之后放开' where warehouseid = '" + WareHouseID + "' and locationnumber= '" + locationnumber + "'";
            int i = DataAccessFactory.Instance.ExecuteNonQuery(CommandType.Text, str, null);
            if (i > 0)
                return true;
            return false;
        }
        public string getFiredoor(string equipmentnumber, ref bool isOpen)
        {
            string sql = "select count(1),locationnumber from location l join equipment e on l.per_equipmentid=e.equipmentid where outflag='S'  AND e.equipmentnumber='" + equipmentnumber + "' limit 1";

            try
            {
                DataSet ds = null;
                ds = DataAccessFactory.Instance.ExecuteDataSet(sql, CommandType.Text, null);
                if (ds == null || ds.Tables[0].Rows.Count < 1 || ds.Tables[0].Rows[0][0].ToString() == "0")
                {
                    isOpen = false;
                    return "";
                }
                else
                {
                    isOpen = true;
                    return ds.Tables[0].Rows[0][1].ToString();
                }
            }
            catch (Exception e)
            {
                isOpen = false;
                return "";
            }
        }
        public string GetTask(string taskno, string barcode, string goodstype, string fromstation, string tostation, string checkinfo, string field1, string field2, string field3, string items, string isfirstone, string islastone, int pallet)
        {
            //OracleParameter[] parms = new OracleParameter[]
            //{
            //    new OracleParameter(@"i_warehouseid",OracleType.VarChar,50),
            //    new OracleParameter(@"i_taskno",OracleType.VarChar,10),
            //    new OracleParameter(@"i_barcode",OracleType.VarChar,50),
            //    new OracleParameter(@"i_goodstype",OracleType.VarChar,10),
            //    new OracleParameter(@"i_fromstation",OracleType.VarChar,10),
            //    new OracleParameter(@"i_tostation",OracleType.VarChar,10),
            //    new OracleParameter(@"i_checkinfo",OracleType.VarChar,10),
            //    //new OracleParameter(@"i_field1",OracleType.VarChar,10),
            //    //new OracleParameter(@"i_field2",OracleType.VarChar,10),
            //    //new OracleParameter(@"i_field3",OracleType.VarChar,10),
            //    new OracleParameter(@"i_items",OracleType.VarChar,900),
            //    new OracleParameter(@"i_isfirstone",OracleType.VarChar,900),
            //    new OracleParameter(@"i_islastone",OracleType.VarChar,900),
            //    new OracleParameter(@"o_result",OracleType.VarChar,5),
            //    new OracleParameter(@"o_msg",OracleType.VarChar,1000)
            //};
            //for (int i = 0; i < 10; i++)
            //{
            //    parms[i].Direction = ParameterDirection.Input;
            //}
            //parms[10].Direction = ParameterDirection.Output;
            //parms[11].Direction = ParameterDirection.Output;
            //parms[0].Value = WareHouseID;
            //parms[1].Value = taskno;
            //parms[2].Value = barcode;
            //parms[3].Value = goodstype;
            //parms[4].Value = fromstation;
            //parms[5].Value = tostation;
            //parms[6].Value = checkinfo;           
            //parms[7].Value = items;
            //parms[8].Value = isfirstone;
            //parms[9].Value = islastone;           
            //parms[11].Value = null;

            List<DbParameter> lstParam = new List<DbParameter>();
            lstParam.Add(new System.Data.OracleClient.OracleParameter { Value = WareHouseID, ParameterName = @"i_warehouseid", Direction = ParameterDirection.Input });
            lstParam.Add(new System.Data.OracleClient.OracleParameter { Value = taskno, ParameterName = @"i_taskno", Direction = ParameterDirection.Input });
            lstParam.Add(new System.Data.OracleClient.OracleParameter { Value = barcode, ParameterName = @"i_barcode", Direction = ParameterDirection.Input });
            lstParam.Add(new System.Data.OracleClient.OracleParameter { Value = goodstype, ParameterName = @"i_goodstype", Direction = ParameterDirection.Input });
            lstParam.Add(new System.Data.OracleClient.OracleParameter { Value = fromstation, ParameterName = @"i_fromstation", Direction = ParameterDirection.Input });
            lstParam.Add(new System.Data.OracleClient.OracleParameter { Value = tostation, ParameterName = @"i_tostation", Direction = ParameterDirection.Input });
            lstParam.Add(new System.Data.OracleClient.OracleParameter { Value = checkinfo, ParameterName = @"i_checkinfo", Direction = ParameterDirection.Input });
            lstParam.Add(new System.Data.OracleClient.OracleParameter { Value = items, ParameterName = @"i_items", Direction = ParameterDirection.Input });
            lstParam.Add(new System.Data.OracleClient.OracleParameter { Value = checkinfo, ParameterName = @"i_isfirstone", Direction = ParameterDirection.Input });
            lstParam.Add(new System.Data.OracleClient.OracleParameter { Value = items, ParameterName = @"i_islastone", Direction = ParameterDirection.Input });
            lstParam.Add(new System.Data.OracleClient.OracleParameter { Value = items, ParameterName = @"o_result", Direction = ParameterDirection.Output });
            lstParam.Add(new System.Data.OracleClient.OracleParameter { Value = null, ParameterName = @"o_msg", Direction = ParameterDirection.Output, OracleType = OracleType.VarChar, Size = 1000 });
            DataAccessFactory.Instance.ExecuteDataSet("cwcs_gettaskinfo", CommandType.StoredProcedure, lstParam.ToArray());
            return lstParam[11].Value.ToString();
        }

        public DataSet GetTaskInfo(string tasktype, string taskstatus, string fromtime, string totime, string wmstaskno, string taskno, string fromstation, string tostation, string barcode, string zonecode)
        {
            string warehouseid = "Z";
            OracleParameter[] parms = new OracleParameter[]
            {
                new OracleParameter(@"i_warehouseid", OracleType.VarChar, 50),
                new OracleParameter(@"i_tasktype", OracleType.VarChar,20),
                new OracleParameter(@"i_taskstatus", OracleType.VarChar,20),
                new OracleParameter(@"i_fromtime", OracleType.VarChar, 20),
                new OracleParameter(@"i_totime", OracleType.VarChar, 20),
                new OracleParameter(@"i_wmstaskno",OracleType.VarChar,50),
                new OracleParameter(@"i_fromstation",OracleType.VarChar,30),
                new OracleParameter(@"i_tostation",OracleType.VarChar,30),
                new OracleParameter(@"i_taskno",OracleType.VarChar,20),
                new OracleParameter(@"i_barcode",OracleType.VarChar,50),
                new OracleParameter(@"i_zonecode",OracleType.VarChar,20),
                new OracleParameter(@"o_cursor", OracleType.Cursor)
            };
            for (int i = 0; i < 10; i++)
            {
                parms[i].Direction = ParameterDirection.Input;
            }
            parms[0].Value = warehouseid;
            parms[1].Value = tasktype;
            parms[2].Value = taskstatus;
            parms[3].Value = fromtime;
            parms[4].Value = totime;
            parms[5].Value = wmstaskno;
            parms[6].Value = fromstation;
            parms[7].Value = tostation;
            parms[8].Value = taskno;
            parms[9].Value = barcode;
            parms[10].Value = zonecode;

            parms[11].Direction = ParameterDirection.Output;

            return DataAccessFactory.Instance.ExecuteDataSet("CWCS_TaskView_P", CommandType.StoredProcedure, parms);


            //List<DbParameter> lstParam = new List<DbParameter>();
            ////lstParam.Add(new System.Data.OracleClient.OracleParameter { Value = WareHouseID, ParameterName = @"i_warehouseid", Direction = ParameterDirection.Input });
            //lstParam.Add(new System.Data.OracleClient.OracleParameter { Value = tasktype, ParameterName = @"i_tasktype", Direction = ParameterDirection.Input });
            //lstParam.Add(new System.Data.OracleClient.OracleParameter { Value = taskstatus, ParameterName = @"i_taskstatus", Direction = ParameterDirection.Input });
            //lstParam.Add(new System.Data.OracleClient.OracleParameter { Value = fromtime, ParameterName = @"i_fromtime", Direction = ParameterDirection.Input });
            //lstParam.Add(new System.Data.OracleClient.OracleParameter { Value = totime, ParameterName = @"i_totime", Direction = ParameterDirection.Input });
            //lstParam.Add(new System.Data.OracleClient.OracleParameter { Value = wmstaskno, ParameterName = @"i_wmstaskno", Direction = ParameterDirection.Input });
            //lstParam.Add(new System.Data.OracleClient.OracleParameter { Value = fromstation, ParameterName = @"i_fromstation", Direction = ParameterDirection.Input });
            //lstParam.Add(new System.Data.OracleClient.OracleParameter { Value = tostation, ParameterName = @"i_tostation", Direction = ParameterDirection.Input });
            //lstParam.Add(new System.Data.OracleClient.OracleParameter { Value = taskno, ParameterName = @"i_taskno", Direction = ParameterDirection.Input });
            //lstParam.Add(new System.Data.OracleClient.OracleParameter { Value = barcode, ParameterName = @"i_barcode", Direction = ParameterDirection.Input });
            //lstParam.Add(new System.Data.OracleClient.OracleParameter { Value = null, ParameterName = @"o_cursor", Direction = ParameterDirection.Output, OracleType = OracleType.Cursor });
            //return DataAccessFactory.Instance.ExecuteDataSet("wcs_taskview_p", CommandType.StoredProcedure, lstParam.ToArray());

        }
        public string[] GetTostation(string taskno, string equipmenttype)
        {
            string sql = "select COUNT(1),fromstation, tostation, barcode,field5,taskstatus from wcs_subtaskinfo where warehouseid ='" + WareHouseID + "' AND EQUIPMENTTYPE ='" + equipmenttype + "' AND TASKNO='" + taskno + "' and taskstatus in('0','2') AND runstatus='1' ";
            string[] ss = new string[5] { "0", "0", "0", "0" ,""};
            try
            {
                DataSet ds = DataAccessFactory.Instance.ExecuteDataSet(sql, CommandType.Text, null);
                if (ds != null)
                {
                    DataTable dt = ds.Tables[0];
                    if (dt.Rows.Count > 0 && Int32.Parse(dt.Rows[0][0].ToString()) > 0)
                    {
                        ss[0] = dt.Rows[0][1].ToString();
                        ss[1] = dt.Rows[0][2].ToString();
                        ss[2] = dt.Rows[0][3].ToString();
                        ss[3] = dt.Rows[0][4].ToString();
                        ss[4] = dt.Rows[0][5].ToString();
                    }
                    else
                    {
                        return null;
                    }
                }
                else
                {
                    return null;
                }
            }
            catch (Exception e)
            {
                return null;
            }
            return ss;
        }
        public DataSet GetTostation(string taskno, string equipmentType, string taskstatus)
        {
            string[] ss = new string[4];
            MySqlParameter[] parms = new MySqlParameter[]
             {
              new MySqlParameter(":Taskno", MySqlDbType.VarChar, 50),
              new MySqlParameter(":EquipmentType",MySqlDbType.VarChar),
              new MySqlParameter(":TaskStatus",MySqlDbType.VarChar)
             };
            parms[0].Value = taskno;
            parms[1].Value = equipmentType;
            parms[2].Value = taskstatus;
            parms[0].Direction = ParameterDirection.Input;
            parms[1].Direction = ParameterDirection.Input;
            parms[2].Direction = ParameterDirection.Input;
            return DataAccessFactory.Instance.ExecuteDataSet(SQL_SELECT_TOSTATION, CommandType.Text, parms);
        }

        public bool AddTask(string fromstation, string tostation)
        {
            MySqlParameter[] parms = new MySqlParameter[]
            {
                new MySqlParameter(@"i_WarehouseID", MySqlDbType.VarChar, 50),
                new MySqlParameter(@"i_fromstation", MySqlDbType.VarChar, 20),
                new MySqlParameter(@"i_tostation", MySqlDbType.VarChar, 20)
            };
            for (int i = 0; i < 3; i++)
            {
                parms[i].Direction = ParameterDirection.Input;
            }
            parms[0].Value = WareHouseID;
            parms[1].Value = fromstation;
            parms[2].Value = tostation;
            DataAccessFactory.Instance.ExecuteNonQuery(CommandType.StoredProcedure, "WCS_ADDTASKH_P", parms);
            return true;
        }

        public string CheckItemCode(string stationno, string taskno, string itemcode)
        {
            OracleParameter[] parms = new OracleParameter[]
            {
                new OracleParameter(@"i_WarehouseID",OracleType.VarChar,50),
                new OracleParameter(@"i_station",OracleType.VarChar,10),
                new OracleParameter(@"i_taskno",OracleType.VarChar,10),
                new OracleParameter(@"i_itemcode",OracleType.VarChar,50),
                new OracleParameter(@"o_result",OracleType.VarChar,10)
            };
            parms[0].Direction = ParameterDirection.Input;
            parms[0].Value = WareHouseID;
            parms[1].Direction = ParameterDirection.Input;
            parms[1].Value = stationno;
            parms[2].Direction = ParameterDirection.Input;
            parms[2].Value = taskno;
            parms[3].Direction = ParameterDirection.Input;
            parms[3].Value = itemcode;
            parms[4].Direction = ParameterDirection.Output;
            parms[4].Value = "0";

            DataAccessFactory.Instance.ExecuteNonQuery("WCS_I_CHECKBARCODE_P", CommandType.StoredProcedure, parms);
            return parms[4].Value.ToString();
        }

        public DataSet GetEventLog(string startTime, string endTime, string keyword, string containerBarcode, string taskNo, bool IsException)
        {

            string sql = @"select t.CREATETIME,t.EVENTLOG from wcs_eventlog t where 1 = 1 ";

            if (startTime != null && !string.IsNullOrEmpty(startTime.Trim()))
            {
                sql += @" and t.createtime >= to_date('{startTime.Trim()}','yyyy/mm/dd hh24:mi:ss')";
            }
            if (endTime != null && !string.IsNullOrEmpty(endTime.Trim()))
            {
                sql += @" and t.createtime <= to_date('{endTime.Trim()}','yyyy/mm/dd hh24:mi:ss')";
            }
            if (keyword != null && !string.IsNullOrEmpty(keyword.Trim()))
            {
                sql += @" and t.eventlog like '%{keyword.Trim()}%'";
            }
            if (containerBarcode != null && !string.IsNullOrEmpty(containerBarcode.Trim()))
            {
                sql += @" and t.eventlog like '%{containerBarcode.Trim()}%'";
            }
            if (taskNo != null && !string.IsNullOrEmpty(taskNo.Trim()))
            {
                sql += @" and t.eventlog like '%{taskNo.Trim()}%'";
            }
            if (IsException)
            {
                sql += @" and t.eventlog like '%失败%'";
            }
            sql += @"order by t.createtime desc";
            return DataAccessFactory.Instance.ExecuteDataSet(sql, CommandType.Text, null);
        }

        //
        public DataSet GetBarcodeHistoryRoute(string startTime, string endTime, string barcode)
        {
            string sql = @"select t.CREATETIME,t.EVENTLOG from wcs_eventlog t where 1 = 1 ";
            if (startTime != null && !string.IsNullOrEmpty(startTime.Trim()))
            {
                sql += @" and t.createtime >= to_date('{startTime.Trim()}','yyyy/mm/dd hh24:mi:ss')";
            }
            if (endTime != null && !string.IsNullOrEmpty(endTime.Trim()))
            {
                sql += @" and t.createtime <= to_date('{endTime.Trim()}','yyyy/mm/dd hh24:mi:ss')";
            }
            if (barcode != null && !string.IsNullOrEmpty(barcode.Trim()))
            {
                sql += @" and t.eventlog like '%{barcode.Trim()}%'";
            }
            sql += @" order by t.createtime asc";
            return DataAccessFactory.Instance.ExecuteDataSet(sql, CommandType.Text, null);
        }

        public DataSet GetItemCode(string taskno)
        {
            OracleParameter[] parms = new OracleParameter[]
            {
                new OracleParameter(PARAM_WAREHOUSE_ID, OracleType.VarChar, 50),
                new OracleParameter(PARAM_TASKNO, OracleType.VarChar, 10)
            };
            parms[0].Direction = ParameterDirection.Input;
            parms[0].Value = WareHouseID;
            parms[1].Direction = ParameterDirection.Input;
            parms[1].Value = taskno;
            return DataAccessFactory.Instance.ExecuteDataSet(SQL_SELECT_ITEMCODE, CommandType.Text, parms);

        }


        public string GetEquipmentNumber(string EquipmentNumber)
        {
            OracleParameter[] parms = new OracleParameter[]
            {
                new OracleParameter(PARAM_WAREHOUSE_ID, OracleType.VarChar, 50),
                new OracleParameter(PARAM_EQUIPMENTNUMBER, OracleType.VarChar, 10)
            };
            parms[0].Direction = ParameterDirection.Input;
            parms[0].Value = WareHouseID;
            parms[1].Direction = ParameterDirection.Input;
            parms[1].Value = "SC" + EquipmentNumber;
            object ob = DataAccessFactory.Instance.ExecuteScalar(CommandType.Text, SQL_SELECT_EQUIPMENT, parms);
            if (ob != null)
                return ob.ToString();
            else
                return "";
        }

        public string GetBarcode(string taskno)
        {
            OracleParameter[] parms = new OracleParameter[]
            {
                new OracleParameter(PARAM_WAREHOUSE_ID, OracleType.VarChar, 50),
                new OracleParameter(PARAM_TASKNO, OracleType.VarChar, 10)
            };
            parms[0].Direction = ParameterDirection.Input;
            parms[0].Value = WareHouseID;
            parms[1].Direction = ParameterDirection.Input;
            parms[1].Value = taskno;
            object ob = DataAccessFactory.Instance.ExecuteScalar(CommandType.Text, SQL_SELECT_BARCODE, parms);
            if (ob != null)
                return ob.ToString();
            else
                return "0";
        }

        public string GetTostationFinal(string taskno)
        {
            OracleParameter[] parms = new OracleParameter[]
            {
                new OracleParameter(PARAM_WAREHOUSE_ID, OracleType.VarChar, 50),
                new OracleParameter(PARAM_TASKNO, OracleType.VarChar, 10)
            };
            parms[0].Direction = ParameterDirection.Input;
            parms[0].Value = WareHouseID;
            parms[1].Direction = ParameterDirection.Input;
            parms[1].Value = taskno;
            object ob = DataAccessFactory.Instance.ExecuteScalar(CommandType.Text, SQL_SELECT_Tostaion, parms);
            if (ob != null)
                return ob.ToString();
            else
                return "0";
        }

        public string GetLedText(string taskno)
        {
            OracleParameter[] parms = new OracleParameter[]
            {
                new OracleParameter(PARAM_WAREHOUSE_ID,OracleType.VarChar,50),
                new OracleParameter(PARAM_TASKNO,OracleType.VarChar,10)
            };
            parms[0].Direction = ParameterDirection.Input;
            parms[0].Value = WareHouseID;
            parms[1].Direction = ParameterDirection.Input;
            parms[1].Value = taskno;
            object ob = DataAccessFactory.Instance.ExecuteScalar(CommandType.Text, SQL_SELECT_LEDTEXT, parms);
            if (ob != null)
                return ob.ToString();
            else
                return "";
        }


        public bool InsertEventlog(string logtext)
        {
            string str = "INSERT INTO wcs_eventlog(warehouseid, eventlog, createtime) VALUES ('" + WareHouseID + "', '" + logtext + "', SYSDATE)";

            if (DataAccessFactory.Instance.ExecuteNonQuery(CommandType.Text, str, null) == 1)
                return true;
            return false;
        }

        public string[] GetSTATostation(string taskno, string status)
        {
            string[] ss = new string[5];
            OracleParameter[] parms = new OracleParameter[] { new OracleParameter(":WareHouseID", OracleType.VarChar, 50), new OracleParameter(":Taskno", OracleType.VarChar, 50), new OracleParameter(":Status", OracleType.VarChar, 10) };
            parms[0].Value = WareHouseID;
            parms[1].Value = taskno;
            parms[2].Value = status;
            parms[0].Direction = ParameterDirection.Input;
            parms[1].Direction = ParameterDirection.Input;
            parms[2].Direction = ParameterDirection.Input;
            DataSet ds = DataAccessFactory.Instance.ExecuteDataSet(SQL_SELECT_STATOSTATION, CommandType.Text, parms);
            if (ds != null)
            {
                DataTable dt = ds.Tables[0];
                if (dt.Rows.Count > 0)
                {
                    ss[0] = dt.Rows[0][0].ToString();
                    ss[1] = dt.Rows[0][1].ToString();
                    ss[2] = dt.Rows[0][2].ToString();
                    ss[3] = dt.Rows[0][3].ToString();
                    ss[4] = dt.Rows[0][4].ToString();
                }
                else
                    return null;
            }
            else
                return null;
            return ss;
        }
        public DataTable GetTaskByToStation(string tostation)
        {
            OracleParameter[] parms = new OracleParameter[]
            {
                new OracleParameter(PARAM_WAREHOUSE_ID, OracleType.VarChar, 50),
                new OracleParameter(PARAM_TOSTATION, OracleType.VarChar, 10)
            };
            parms[0].Direction = ParameterDirection.Input;
            parms[0].Value = WareHouseID;
            parms[1].Direction = ParameterDirection.Input;
            parms[1].Value = tostation;
            DataSet ds = DataAccessFactory.Instance.ExecuteDataSet(SQL_SELECT_TASKBYSTATION, CommandType.Text, parms);
            if (ds != null)
                return ds.Tables[0];
            else
                return null;
        }
        public string GetPalletOutBound(string tostation)
        {
            OracleParameter[] parms = new OracleParameter[]
            {
                new OracleParameter(@"i_warehouseid", OracleType.VarChar, 50),
                new OracleParameter(@"i_destination",OracleType.VarChar,20),
                new OracleParameter(@"i_userid",OracleType.VarChar,20),
                new OracleParameter(@"o_result", OracleType.Int32)
            };
            for (int i = 0; i < 3; i++)
            {
                parms[i].Direction = ParameterDirection.Input;
            }
            parms[3].Direction = ParameterDirection.Output;
            parms[0].Value = WareHouseID;
            parms[1].Value = tostation;
            parms[2].Value = "WCS";
            parms[3].Value = 0;
            DataAccessFactory.Instance.ExecuteNonQuery(CommandType.StoredProcedure, "WCS_PALLETOUTBOUND_P", parms);
            return parms[3].Value.ToString();
        }

        public string QueryEmptyPalletTask(string StaNo)
        {
            string str = "select count(1) from wcs_subtaskinfo where toStation='" + StaNo + "' and EquipmentType = 'STA' and warehouseid= :WareHouseID order by TaskNo";

            OracleParameter[] parms = new OracleParameter[]
             {
                new OracleParameter(@"i_warehouseid", OracleType.Char, 32)
             };
            parms[0].Value = WareHouseID;

            object ob = DataAccessFactory.Instance.ExecuteScalar(CommandType.Text, str, parms);
            if (ob != null)
                return ob.ToString();
            return "";

        }

        public bool UpdateSCStatusNTI(string crcnum, string crcstatus)
        {
            string equipmentnumber = crcnum.Replace("CRC00", "SC");
            string str_UpdateSCStatus = "update equipment set statecode='" + crcstatus + "' where equipmentnumber =  '" + equipmentnumber + "'  and  warehouseid = 'Z'";
            int i = DataAccessFactory.Instance.ExecuteNonQuery(CommandType.Text, str_UpdateSCStatus, null);
            return i > 0 ? true : false;
        }


        #region denghaijian新增
        /// <summary>
        /// 入库检测站台 检测分配的堆垛机是否有故障
        /// </summary>
        /// <param name="taskNo"></param>
        /// <returns></returns>
        public string CheckSCstatus(string taskNo)
        {
            try
            {
                string sql = "select w.field1 from wcs_taskinfo w left join equipment e on w.field1=e.description where w.taskno='" + taskNo + "' and length(w.taskstatus) < 1 and e.typecode='Equipment_AS' and e.statecode <> 'Equipment_OK' ";
                Object scNo = DataAccessFactory.Instance.ExecuteScalar(sql);
                if (scNo != null)
                {
                    return scNo.ToString();
                }
                return "";
            }
            catch (Exception e)
            {

                throw e;
            }
        }
        /// <summary>
        /// 获取拣选站台已经分配任务数量
        /// </summary>
        /// <param name="toStationNo"></param>
        /// <returns></returns>
        public int GetPickQueryCount(string toStationNo)
        {
            string sql = "select count(1) from wcs_subtaskinfo aa  where aa.equipmenttype='SC'  and aa.taskstatus<>'0' and aa.currentstation =" + toStationNo;
            Object count = DataAccessFactory.Instance.ExecuteScalar(sql);
            if (count != null)
            {
                return Convert.ToInt32(count);
            }
            return 0;
        }
        /// <summary>
        /// 根据申请站台获取是否登录了工作台
        /// </summary>
        /// <param name="stationNo"></param>
        /// <returns></returns>
        public bool GetDeskIsOn(string stationNo)
        {
            string sql = @"select code from desk where code = (select pickdeskcode from pickingstation where pickstation='{stationNo}') and state='ON' and ISPICK=1";
            Object count = DataAccessFactory.Instance.ExecuteScalar(sql);
            if (count != null)
            {
                return true;
            }
            return false;
        }
        /// <summary>
        /// 根据申请站台获取是否登录了的双工位工作台
        /// </summary>
        /// <param name="stationNo"></param>
        /// <returns></returns>
        public bool GetDeskIsDouble(string stationNo)
        {
            string sql = @"select code from desk where code = (select pickdeskcode from pickingstation where pickstation='{stationNo}') and state='ON' and ISPICK=1 and ISDOUBLE=1";
            Object count = DataAccessFactory.Instance.ExecuteScalar(sql);
            if (count != null)
            {
                return true;
            }
            return false;
        }
        /// <summary>
        /// 根据wcs任务号获取wms任务号
        /// </summary>
        /// <param name="taskNo"></param>
        /// <returns></returns>
        public string GetWMSTaskNoByWCSTaskNo(int taskNo)
        {
            string result = "";
            string sql = @"select wmstaskno from wcs_taskinfo where taskno='{taskNo}'";
            Object wmsTaskNo = DataAccessFactory.Instance.ExecuteScalar(sql);
            if (wmsTaskNo != null)
            {
                result = wmsTaskNo.ToString();
            }
            return result;
        }
        #endregion
    }
}
